import * as mysql from 'mysql2/promise';
import config from '../config';
import { logToDatabase, LogType, LogLevel, consoleLog } from '../utils/logger';
import { utils } from '../utils/utils';

let pool: mysql.Pool;

/**
 * 初始化MySQL连接池
 */
export function initMySQL(): mysql.Pool {
  if (!pool) {
    pool = mysql.createPool({
      host: config.mysql.host,
      port: config.mysql.port,
      user: config.mysql.user,
      password: config.mysql.password,
      database: config.mysql.database,
      waitForConnections: true,
      connectionLimit: config.mysql.connectionLimit,
      queueLimit: 0,
      enableKeepAlive: true,
      keepAliveInitialDelay: 0,
    });

    // 监听连接事件
    // 注意：mysql2 的 Pool 类型定义可能不完整，使用类型断言
    (pool as any).on('connection', (connection: any) => {
      logToDatabase(
        utils.generateUUID(),
        LogType.MYSQL,
        LogLevel.INFO,
        'MySQL连接已建立',
        {
          threadId: connection.threadId,
          host: config.mysql.host,
          port: config.mysql.port,
          database: config.mysql.database,
        }
      ).catch(() => {});
    });

    // 监听连接错误事件
    // mysql2 的 Pool 可能不支持直接的 error 事件，改为在连接级别监听
    (pool as any).on('error', (err: any) => {
      const requestId = utils.generateUUID();
      logToDatabase(
        requestId,
        LogType.MYSQL,
        LogLevel.ERROR,
        `MySQL连接池错误: ${err?.message || String(err)}`,
        {
          code: err?.code,
          errno: err?.errno,
          sqlState: err?.sqlState,
          sqlMessage: err?.sqlMessage,
        }
      ).catch(() => {});
      consoleLog(
        LogType.MYSQL,
        LogLevel.ERROR,
        `MySQL连接池错误: ${err?.message || String(err)}`,
        {
          requestId,
          error: err,
          host: config.mysql.host,
          port: config.mysql.port,
        }
      );
    });
  }
  return pool;
}

/**
 * 获取MySQL连接池
 */
export function getMySQLPool(): mysql.Pool {
  if (!pool) {
    return initMySQL();
  }
  return pool;
}

/**
 * 执行查询（带日志记录）
 */
export async function query<T = any>(sql: string, params?: any[], requestId?: string): Promise<T[]> {
  const start = Date.now();
  const logRequestId = requestId || utils.generateUUID();
  const pool = getMySQLPool();
  
  try {
    const [rows] = await pool.execute(sql, params);
    const duration = Date.now() - start;
    
    // 记录成功的查询日志
    await logToDatabase(
      logRequestId,
      LogType.MYSQL,
      LogLevel.INFO,
      `SQL查询成功 - ${duration}ms`,
      {
        sql: sql.substring(0, 500), // 限制SQL长度
        params: params ? JSON.stringify(params) : null,
        duration,
        rowsAffected: Array.isArray(rows) ? rows.length : 0,
      }
    ).catch(() => {});
    
    return rows as T[];
  } catch (err: any) {
    const duration = Date.now() - start;
    
    // 判断错误类型
    let level = LogLevel.ERROR;
    let message = `SQL查询失败: ${err.message}`;
    
    if (err.code === 'ETIMEDOUT' || err.message?.includes('timeout')) {
      message = `SQL查询超时: ${err.message}`;
    } else if (err.code === 'ER_PARSE_ERROR' || err.code === 'ER_SYNTAX_ERROR') {
      message = `SQL语法错误: ${err.message}`;
      level = LogLevel.ERROR;
    } else if (err.code === 'ECONNREFUSED' || err.code === 'ENOTFOUND') {
      message = `MySQL连接失败: ${err.message}`;
      level = LogLevel.ERROR;
    }
    
    // 记录错误日志
    await logToDatabase(
      logRequestId,
      LogType.MYSQL,
      level,
      message,
      {
        sql: sql.substring(0, 500),
        params: params ? JSON.stringify(params) : null,
        duration,
        error: {
          code: err.code,
          errno: err.errno,
          sqlState: err.sqlState,
          sqlMessage: err.sqlMessage,
          message: err.message,
        },
      }
    ).catch(() => {});
    
    throw err;
  }
}

/**
 * 执行单个查询（带日志记录）
 */
export async function queryOne<T = any>(sql: string, params?: any[], requestId?: string): Promise<T | null> {
  const results = await query<T>(sql, params, requestId);
  return results.length > 0 ? results[0] : null;
}

/**
 * 测试MySQL连接
 */
export async function testMySQLConnection(): Promise<boolean> {
  try {
    const pool = getMySQLPool();
    await pool.execute('SELECT 1');
    
    // 记录连接测试成功
    await logToDatabase(
      utils.generateUUID(),
      LogType.MYSQL,
      LogLevel.INFO,
      'MySQL连接测试成功',
      {
        host: config.mysql.host,
        port: config.mysql.port,
        database: config.mysql.database,
      }
    ).catch(() => {});
    
    return true;
  } catch (error: any) {
    // 记录连接测试失败
    await logToDatabase(
      utils.generateUUID(),
      LogType.MYSQL,
      LogLevel.ERROR,
      `MySQL连接测试失败: ${error.message}`,
      {
        host: config.mysql.host,
        port: config.mysql.port,
        database: config.mysql.database,
        error: {
          code: error.code,
          message: error.message,
        },
      }
    ).catch(() => {});
    
    consoleLog(
      LogType.MYSQL,
      LogLevel.ERROR,
      `MySQL连接测试失败: ${error?.message || String(error)}`,
      {
        error: error,
        host: config.mysql.host,
        port: config.mysql.port,
        database: config.mysql.database,
      }
    );
    return false;
  }
}

